<?php
namespace app\console\controller;
use app\model\Express;
use app\model\Order as SelfModel;
use app\model\Member;
use app\model\DistributionType;

use app\model\OrderGoods;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\facade\Request;
use think\facade\Route;
use think\facade\View;
use think\facade\Db;
use think\facade\Cache;
use think\facade\Cookie;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Order extends Base
{


    /**
     * @return mixed
     * 查询
     */
    public function index()
    {

        $param = Request::param();//withTrashed()->
        unset($param['rand']);
        unset($param['page']);
        $where[] = ['status','>=',0];
//        $where[] = ['type_id','=',1];
//        $where = [];
//        $dat = (new SelfModel())->select();
//        foreach ($dat as $v){
//            $goods_ids = OrderGoods::where('order_number',$v['order_number'])->column('goods_id');
//            SelfModel::where('order_number',$v['order_number'])->update(['goods_data'=>implode(',',$goods_ids)]);
//        }

        $model = (new SelfModel())->where($where)->withSearch(array_keys($param),$param);
        $data = $model->order(['id'=>'desc'])->paginate(['query' => $param,'list_rows'=>input('list_rows',20)])->each(function ($item,$key){

        })->append(['status_text','distribution_type','goodsabc','member_name','hospital']);
//        halt($data);

        if(!empty($param['list_rows'])){
            $this->write($data->toArray()['data']);
        }

        $total_price = $model->sum('total_price');
        $total_num = $model->count();

        $this->select();
//        if(!empty(input('zg_id'))){
//            $jg = model('Manage')->where('role_id',2)->where('parent_id',input('zg_id'))->select();
//        }else{
//            $jg = model('Manage')->where('role_id',2)->select();
//        }

        $goods = \app\model\Goods::select();

        $goods_tongji = $model->select();

        $goods_sum = 0;
        $goods_sum2 = 0;
        foreach ($goods_tongji as $v){
            $where =[];
            if(!empty($param['goods_data'])){
                $where[] = ['goods_id','=',$param['goods_data']];
            }
            $order_goods =  OrderGoods::where('order_id',$v['id'])->where($where)->select();
            foreach ($order_goods as $v2){
                $goods_sum2 += $v2['number2']*$v2['number'];
                $goods_sum += $v2['number'];
            }

        }
      // dump($d);
//        if(!empty($param['goods_data'])){
//            $goods_sum = OrderGoods::where('goods_id',$param['goods_data'])->sum('number');
//            $goods_sum2 = OrderGoods::where('goods_id',$param['goods_data'])->sum('number2');
//        }

        return View::fetch('', ['data' => $data,'total_price'=>$total_price,'total_num'=>$total_num,'status'=> (new SelfModel())->console_status,'goods'=>$goods,'goods_sum'=>$goods_sum,'goods_sum2'=>$goods_sum2]);
    }

    /**
     * 添加
     * @return mixed
     */
    public function create()
    {

        if ($this->request->isPost()) {
            $inquire = $this->request->param();
            $ret = (new SelfModel())->save($inquire);
            if ($ret) {
                win('操作成功', Route::buildUrl('index'));
            }
            fail('操作失败');
        }
        $this->select();
        return View::fetch('', ['info' => []]);
    }

    /**
     * 修改
     * @param $id
     * @return mixed
     */
    public function update()
    {
        $param = $this->request->param();
        //dump($param);
        $info = SelfModel::find($param['id']);
        $order_goods = (new OrderGoods())->where('order_number', $info['order_number'])->group('package_id')->select();
        foreach ($order_goods as &$v){
            $v['goods_list'] = (new OrderGoods())->where('package_id', $v['package_id'])->where('order_id', $v['order_id'])->select();
        }
        $info['package'] = $order_goods;
//        halt($info->toArray());

        if ($this->request->isPost()) {

            $status = $info['status'];

            if($status == 2){ //待发货
                $param['status'] = 3; //已发货
            }
            if($status == 4){ //待自提
                $param['status'] =7; //已收货
            }

            $ret = $info->save($param);
            if ($ret !== false) {
                win('修改成功', Route::buildUrl('index'));
            }
            fail('修改失败');
        }

        $this->select();

        $express = Express::where('status',1)->select();
        return View::fetch('create', ['info' => $info, 'express' => $express]);
    }

    public function select()
    {


        View::assign('status', (new SelfModel())->console_status);
        //$member = Member::where([['status', '=', 1]])->order(['sort' => 'asc', 'id' => 'desc'])->select()->toArray();
        View::assign('member', []);
        $distribution_type = DistributionType::where([['status', '=', 1]])->order(['sort' => 'asc', 'id' => 'desc'])->select()->toArray();


        View::assign('distribution_type', $distribution_type);

    }

    /**
     * 删除方法
     * @param $id
     */
    public function destory($id)
    {
        if ($this->request->isGet()) {
            $ret = SelfModel::destroy($id);
            if ($ret) jump('删除成功', Route::buildUrl('index'));
            fail('删除失败');
        }
    }

    /**
     * 软删除数据恢复方法
     * @param $id
     */
    public function restore($id)
    {
        $user = SelfModel::onlyTrashed()->find($id);
        $ret = $user->restore();
        if ($ret === true) {
            jump('恢复成功', Route::buildUrl('index'));
        }
    }

    /**
     * 公共切换状态
     * @return bool|int
     */
    public function check()
    {
        $inquire = $this->request->param();
        $ret = (new SelfModel())->where('id', $inquire['id'])->value($inquire['check']) == '1' ? 2 : 1;
        if ((new SelfModel())->where('id', $inquire['id'])->update([$inquire['check'] => $ret])) {
            return $ret;
        }
        return false;
    }


    /**
     * 首页批量更新
     */
    public function index_update()
    {
        $param = $this->request->param();
        $data = [];
        if(!empty($param['sort'])){
            foreach ($param['sort'] as $k => $v) {
                $data[$k]['id'] = $k;
                $data[$k]['sort'] = $v;
                // $ret = Db::name($this->controller)->where('id',$k)->setField('sort',$v);
            }

        }
        $ret = (new SelfModel())->allowField(['id','sort'])->saveAll($data);
        if ($ret !== false) {
            jump('更新成功', Route::buildUrl('index'));
        }
    }

    public function setField()
    {
        $param = Request::param();

        $ret = SelfModel::where('id', $param['id'])->save([$param['field'] => $param['value']]);

        if ($ret) {
            return json(['code' => 200, 'message' => '修改成功']);
        }
        return json(['code' => 400, 'message' => '没有修改']);
    }




    public function write($data = [], $filename = '订单导出', $set_title = '表格1')
    {

        //$filename =  '订单导出';
        //$set_title = '订单导出';
        require_once root_path() . 'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        define('EOL', '<br />');
        $objPHPExcel = new \PHPExcel();
        ini_set("memory_limit", "1024M"); // 设置php可使用内存
        set_time_limit(0);
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        /*----------------------- 设置导出标题 ------------------------*/
        $objPHPExcel->setActiveSheetIndex(0);
        $az = [
            'A' => 30,
            'B' => 20,
            'C' => 20,
            'D' => 20,
            'E' => 20,
            'F' => 20,
            'G' => 20,
            'H' => 20,
            'I' => 30,
            'J' => 20,
            'K' => 20,
            'L' => 20,
            'M' => 200,
            'N' => 20,
            'O' => 20,
            'P' => 20,
            'Q' => 20,
            'R' => 20,
            'S' => 20,
            'T' => 20,
            'U' => 20,
            'V' => 20,
            'W' => 20,
            'X' => 20,
            'Y' => 20,
            'Z' => 20
        ];
        $az2 = array_keys($az);//给data数组循环使用 0 => 'A'
        $th = [
            '登录手机号' => 'member_mobile',
            '登录人姓名' => 'member_name',
            '登录人单位' => 'hospital',
            '订单号' => 'order_number',
            '姓名' => 'name',
            '手机号' => 'mobile',
            '订单价格' => 'order_price',
            '配送方式' => 'distribution_type',
            '省市区' => 'pca',
            '地址' => 'address',
            '状态' => 'status_text',
            '创建时间' => 'create_time',
            '商品列表' => 'goods_list'
        ];
        $th2 = array_keys($th);
        $th3 = array_values($th); //索引数组转关联数组 0 => 'order_number'
        $az_i = 0;

        foreach ($az as $az_k => $az_v) { //表格全部列循环
            //设置第一列导出标题
            if (!empty($th2[$az_i])) {
                $objPHPExcel->getActiveSheet()->setCellValue($az_k . '1', $th2[$az_i]);// A1 A2 A3...
                //设置单元列的宽度
                $objPHPExcel->getActiveSheet()->getColumnDimension($az_k)->setWidth($az_v);
                $az_i++;
            }

        }

        foreach ($data as $k => $v) {
            $i = $k + 2; //循环列 123
            $k2_i = 0;//循环行 123

            foreach ($th3 as $ava) {
                $objPHPExcel->getActiveSheet()->getStyle($az2[$k2_i] . ($i))->getAlignment()->setWrapText(true); //控制折行
                $objPHPExcel->getActiveSheet()->setCellValue($az2[$k2_i] . ($i), "\t" . $v[$ava]);//循环行
                $k2_i++;
            }
        }


        $objPHPExcel->getActiveSheet()->setTitle($set_title);
        echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;charset=utf-8;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
        echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 完成写入文件", EOL;
        echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
    }

    public function write2($data = [], $filename = '订单导出', $set_title = '表格1')
    {


        //$filename =  '订单导出';
        //$set_title = '订单导出';
        require_once root_path() . 'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        define('EOL', '<br />');
        $objPHPExcel = new \PHPExcel();
        ini_set("memory_limit", "1024M"); //设置php可使用内存
        set_time_limit(0);
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)){
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        /*----------------------- 设置导出标题 ------------------------*/
        $objPHPExcel->setActiveSheetIndex(0);
        $az = [
            'A' => 30,
            'B' => 20,
            'C' => 20,
            'D' => 20,
            'E' => 20
        ];
        $az2 = array_keys($az);//给data数组循环使用 0 => 'A'
        $th = [
            '登录手机号' => 'member_mobile',
            '登录人姓名' => 'member_name',
            '登录人单位' => 'hospital',
            '订单号' => 'order_number',
            '套餐' => 'title'
        ];

        $th2 = array_keys($th);
        $th3 = array_values($th); //索引数组转关联数组 0 => 'order_number'
        $az_i = 0;

        foreach ($az as $az_k => $az_v) { //表格全部列循环
            //设置第一列导出标题
            if (!empty($th2[$az_i])) {
                $objPHPExcel->getActiveSheet()->setCellValue($az_k . '1', $th2[$az_i]);// A1 A2 A3...
                //设置单元列的宽度
                $objPHPExcel->getActiveSheet()->getColumnDimension($az_k)->setWidth($az_v);
                $az_i++;
            }

        }

        foreach ($data as $k => $v) {
            $i = $k + 2; //循环列 123
            $k2_i = 0;//循环行 123

            foreach ($th3 as $ava) {
                $objPHPExcel->getActiveSheet()->getStyle($az2[$k2_i] . ($i))->getAlignment()->setWrapText(true); //控制折行
                $objPHPExcel->getActiveSheet()->setCellValue($az2[$k2_i] . ($i), "\t" . $v[$ava]);//循环行
                $k2_i++;
            }
        }


        $objPHPExcel->getActiveSheet()->setTitle($set_title);
        echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;charset=utf-8;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
        echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 完成写入文件", EOL;
        echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
    }


    public function read(){

     require_once root_path().'extend/phpoffice/phpexcel/Classes/PHPExcel.php';

//        $filename = uploadOne('read','file',true);
//
//        $filename = iconv('utf-8', 'gb2312', $filename);
//
//        if (empty($filename) || !file_exists($filename)){
//            die('file not exists');
//        }
//        // 兼容 Excel03 和 Excel07
//        $objRead = new \PHPExcel_Reader_Excel2007();
//        if (!$objRead->canRead($filename)){
//            $objRead = new \PHPExcel_Reader_Excel5();
//            if (!$objRead->canRead($filename)){
//                die('No Excel');
//            }
//        }

        // 获取 excel 中的文件内容
        $objPHPExcel = \PHPExcel_IOFactory::load($_FILES['read']['tmp_name']);

        // 一次性取出整个 sheet 内的内容（不常用）
//    $sheetCount = $objPHPExcel->getSheetCount();
//    for ($i=0; $i<$sheetCount; $i++){
//        $data = $objPHPExcel->getSheet($i)->toArray();
//        var_dump($data);
//    }
        $da = [];
        // 逐行读取 sheet 内的内容（常用）

        foreach ($objPHPExcel->getWorksheetIterator() as $sheet){   //循环sheet

            foreach ($sheet->getRowIterator() as $row){     //循环row
                if ($row->getRowIndex() <= 1){  // 1的时候默认从第二行开始

                    continue;
                }
                $data = [];
                foreach ($row->getCellIterator() as $cell){     //循环cell

                    $v = $cell->getValue();
                    if($v instanceof \PHPExcel_RichText)  $v = $v->__toString();
                    //$v = str_replace('.0', "", $v);//把数字的.0替换掉 不去掉也可以使用
                    $data[] = $v;
                }

                // $a =  str_replace(.0, "", $data);
//                dump($data[5]);
                $data = unsetNull($data);

               // dump(trim($data[3]));
                $mobile = trim($data[3]);
                $order = \app\model\Order::where('mobile|member_mobile|name',$mobile)->find();



                $str = '';
                //dump($data[5]);
                if(empty($order)){
                    $str .= '订单没有查到 '.$mobile;
                    $data[5] = '是';
                    $data[7] = '';
                    $member = Member::where('mobile',$mobile)->find();
                    if(empty($member)){
                        $data[6] = '是';
                        $str .= ' 会员没有注册 '.$mobile;
                    }
                    echo $str.'<br/>';
                }else{

                    $express_number = trim($data[4]);

                    if(!empty($express_number)){
                       // dump($order['order_number']);
                      \app\model\Order::where('mobile|member_mobile|name',$mobile)->update(['status'=>7,'express_code'=>'jd','express_title'=>'京东','express_number' => $express_number,'time'=>date('Y-m-d')]);
                    }

                    $data[5] = '';
                    $data[6] = '';
                    $data[7] = $order['order_number'];
                }
                //$data[5] = 'AAAA';
               // dump($data);
                $da[] = $data;
//                dump($data);

            }

        }
        $this->write3($da);
//die;
      // dump($da);die;
       // dump($da);

        //halt($da);

        //win('导入成功');

    }

    public function write3($data = [], $filename = '订单导出', $set_title = '表格1')
    {

        //$filename =  '订单导出';
        //$set_title = '订单导出';
        require_once root_path() . 'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        define('EOL', '<br />');
        $objPHPExcel = new \PHPExcel();
        ini_set("memory_limit", "1024M"); //设置php可使用内存
        set_time_limit(0);
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)){
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        /*----------------------- 设置导出标题 ------------------------*/
        $objPHPExcel->setActiveSheetIndex(0);
        $az = [
            'A' => 30,
            'B' => 20,
            'C' => 20,
            'D' => 20,
            'E' => 20,
            'F' => 20,
            'G' => 20,
            'H' => 20
        ];

        $az2 = array_keys($az);//给data数组循环使用 0 => 'A'
        $th = [
            '序号' => '0',
            '商品列表' => '1',
            '姓名' => '2',
            '手机号' => '3',
            '快递单号' => '4',
            '本手机号订单没有查询到' => '5',
            '本手机号会员没有注册' => '6',
            '订单号' => '7',
//            '地址2' => '7',
//            '创建时间' => '8'

        ];

        //halt($th);
        $th2 = array_keys($th);

        $th3 = array_values($th); //索引数组转关联数组 0 => 'order_number'
        $az_i = 0;
        foreach ($az as $az_k => $az_v) { //表格全部列循环
            //设置第一列导出标题
            if (!empty($th2[$az_i])) {
                $objPHPExcel->getActiveSheet()->setCellValue($az_k . '1', $th2[$az_i]);// A1 A2 A3...
                //设置单元列的宽度
                $objPHPExcel->getActiveSheet()->getColumnDimension($az_k)->setWidth($az_v);
                $az_i++;
            }
        }

        foreach ($data as $k => $v) {
            $i = $k + 2; //循环列 123
            $k2_i = 0;//循环行 123

            foreach ($th3 as $ava) {
                $objPHPExcel->getActiveSheet()->getStyle($az2[$k2_i] . ($i))->getAlignment()->setWrapText(true); //控制折行
                $objPHPExcel->getActiveSheet()->setCellValue($az2[$k2_i] . ($i), "\t" . $v[$ava]);//循环行
                $k2_i++;
            }
        }


        $objPHPExcel->getActiveSheet()->setTitle($set_title);
        echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;charset=utf-8;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
        echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 完成写入文件", EOL;
        echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
    }

    public function export($filename='1.xlsx')
    {
        $filename = '订单导出';
        $param = $this->request->param();
        if(!empty($param['create_time'])){
            $filename .= ' '.$param['create_time'];
        }
        $filename .= '.xlsx';
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('数据');
        $header = ['ID','日期','订单号','收件人','联系电话','发货地址','产品名称','数量','订单备注','订单金额','订单状态','是否申请退款'];
        foreach ($header as $kh => $vh){
            $sheet->setCellValueByColumnAndRow($kh+1,1, $vh);
        }



        $data = \app\model\Order::withSearch(array_keys($param), $param)->lock(true)->order(['id'=>'desc'])->select();
        $start_row = 2;

        foreach ($data as $k => $v){
            $objValidation = $sheet->getCellByColumnAndRow(12,$k+1)->getDataValidation();
            // $objValidation = $objActSheet->getCell("{$col}" . $i)->getDataValidation()
            $objValidation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
            $objValidation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
            $objValidation->setAllowBlank(false);
            $objValidation->setShowErrorMessage(true);
            $objValidation->setShowDropDown(true);
            $objValidation->setErrorTitle('Input error');
            $objValidation->setError('Value is not in list.');
            $objValidation->setPromptTitle('Pick from list');
//        $objValidation->setPrompt('Please pick a value from the drop-down list.');
            $express = Express::where('status',1)->column('title');

            $express = implode(',',$express);

            $objValidation->setFormula1('"'.$express.'"');
            $order_goods = OrderGoods::where('order_number',$v['order_number'])->select();
            foreach ($order_goods as $k2=> $v2){
                $sheet->setCellValueByColumnAndRow(7,$start_row+$k2, $v2['title']);
                $sheet->setCellValueByColumnAndRow(8,$start_row+$k2, $v2['number']);
//                $sheet->setCellValueByColumnAndRow(9,$start_row+$k2, $v2['remark']);
            }
            $end_row = $start_row+$order_goods->count()-1;

            $sheet->mergeCellsByColumnAndRow(1,$start_row,1,$end_row)->setCellValueByColumnAndRow(1,$start_row, $v['id']);
            $sheet->mergeCellsByColumnAndRow(2,$start_row,2,$end_row)->setCellValueByColumnAndRow(2,$start_row, $v['create_time']);
            $sheet->mergeCellsByColumnAndRow(3,$start_row,3,$end_row)->setCellValueByColumnAndRow(3,$start_row, $v['order_number']);
            $sheet->mergeCellsByColumnAndRow(4,$start_row,4,$end_row)->setCellValueByColumnAndRow(4,$start_row, $v['name']);
            $sheet->mergeCellsByColumnAndRow(5,$start_row,5,$end_row)->setCellValueByColumnAndRow(5,$start_row, $v['mobile']);
            $sheet->mergeCellsByColumnAndRow(6,$start_row,6,$end_row)->setCellValueByColumnAndRow(6,$start_row, $v['prov'].' '.$v['city'].' '.$v['area'].' '.$v['address']);
            $sheet->mergeCellsByColumnAndRow(9,$start_row,9,$end_row)->setCellValueByColumnAndRow(9,$start_row, $v['remark']);
            $sheet->mergeCellsByColumnAndRow(10,$start_row,10,$end_row)->setCellValueByColumnAndRow(10,$start_row, $v['total_price']);
            $sheet->mergeCellsByColumnAndRow(11,$start_row,11,$end_row)->setCellValueByColumnAndRow(11,$start_row, $v['status_text']);
            $sheet->mergeCellsByColumnAndRow(12,$start_row,12,$end_row)->setCellValueByColumnAndRow(12,$start_row, $v['refund_title']);
            $start_row += $order_goods->count();

        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
        ob_end_clean();//清除缓冲区,避免乱码


    }








}